rm(list=ls())
library(tidyverse)
library(readxl)

# Read and structure PEA for census 1991,2000,2010
file <- list.files(here::here("data","raw","IBGE"), pattern = "tab616_pea", full.names = T)

pea_mun_1991 <- read_excel(file,
                           sheet = "1991",
                           skip = 3,
                           col_names = c("mun_code","mun_name","situation","var_desc","age_group","conditino","sex","pea_perc"),
                           col_types = c(rep("text",7),"numeric"),
                           na = "...") %>% 
  mutate(census_year = 1991,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,pea_perc)


pea_mun_2000 <- read_excel(file,
                           sheet = "2000",
                           skip = 3,
                           col_names = c("mun_code","mun_name","situation","var_desc","age_group","conditino","sex","pea_perc"),
                           col_types = c(rep("text",7),"numeric"),
                           na = "...") %>% 
  mutate(census_year = 2000,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,pea_perc)


pea_mun_2010 <- read_excel(file,
                           sheet = "2010",
                           skip = 3,
                           col_names = c("mun_code","mun_name","situation","var_desc","age_group","conditino","sex","pea_perc"),
                           col_types = c(rep("text",7),"numeric"),
                           na = "...") %>% 
  mutate(census_year = 2010,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,pea_perc)

pea_mun <- bind_rows(pea_mun_1991,pea_mun_2000,pea_mun_2010)

# SaveRdS ----

write_rds(pea_mun, here::here("data","processed","citycharacteristics","pea_mun.rds"))
